/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2015-09-12 23:52:28                          */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('Attachment')
            and   type = 'U')
   drop table Attachment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Chapter')
            and   type = 'U')
   drop table Chapter
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Grade')
            and   type = 'U')
   drop table Grade
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GradeSubject')
            and   type = 'U')
   drop table GradeSubject
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PracticeForm')
            and   type = 'U')
   drop table PracticeForm
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PracticeQuestion')
            and   type = 'U')
   drop table PracticeQuestion
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Question')
            and   type = 'U')
   drop table Question
go

if exists (select 1
            from  sysobjects
           where  id = object_id('QuestionOption')
            and   type = 'U')
   drop table QuestionOption
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Subject')
            and   type = 'U')
   drop table Subject
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Video')
            and   type = 'U')
   drop table Video
go

/*==============================================================*/
/* Table: Attachment                                            */
/*==============================================================*/
create table Attachment (
   aid                  char(64)             not null,
   name                 nvarchar(256)        not null,
   type                 nvarchar(10)         not null,
   fullPath             nvarchar(512)        not null,
   sha1                 char(160)            not null,
   systime              datetime             not null default getdate(),
   constraint PK_ATTACHMENT primary key (aid)
)
go

/*==============================================================*/
/* Table: Chapter                                               */
/*==============================================================*/
create table Chapter (
   cid                  int                  identity,
   gsid                 int                  not null,
   sid                  int                  not null,
   subjectname          nvarchar(50)         not null,
   gid                  int                  not null,
   gname                nvarchar(100)        not null,
   part                 nvarchar(50)         not null,
   title                nvarchar(50)         not null,
   description          nvarchar(255)        null,
   creator              int                  not null,
   systime              datetime             not null default getdate(),
   status               int                  null default 1,
   constraint PK_CHAPTER primary key (cid)
)
go

/*==============================================================*/
/* Table: Grade                                                 */
/*==============================================================*/
create table Grade (
   nid                  int                  identity,
   name                 nvarchar(100)        not null,
   description          nvarchar(255)        null,
   ismenu               bit                  null,
   menuindex            int                  null,
   creator              int                  not null,
   systime              datetime             not null default getdate(),
   status               int                  null default 1,
   constraint PK_GRADE primary key (nid)
)
go

/*==============================================================*/
/* Table: GradeSubject                                          */
/*==============================================================*/
create table GradeSubject (
   gsid                 int                  identity,
   gid                  int                  not null,
   sid                  int                  not null,
   constraint PK_GRADESUBJECT primary key (gsid)
)
go

/*==============================================================*/
/* Table: PracticeForm                                          */
/*==============================================================*/
create table PracticeForm (
   pid                  int                  identity,
   pname                varchar(100)         not null,
   description          varchar(255)         null,
   creator              int                  not null,
   systime              datetime             not null default getdate(),
   constraint PK_PRACTICEFORM primary key (pid)
)
go

/*==============================================================*/
/* Table: PracticeQuestion                                      */
/*==============================================================*/
create table PracticeQuestion (
   pqid                 int                  identity,
   pid                  int                  not null,
   qid                  int                  not null,
   constraint PK_PRACTICEQUESTION primary key (pqid)
)
go

/*==============================================================*/
/* Table: Question                                              */
/*==============================================================*/
create table Question (
   qid                  int                  identity,
   type                 int                  not null,
   title                nvarchar(255)        null,
   content              nvarchar(2000)       null,
   score                numeric(4,1)         not null,
   parentid             int                  null,
   creator              int                  not null,
   systime              datetime             not null default getdate(),
   status               int                  not null default 1,
   constraint PK_QUESTION primary key (qid)
)
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('Question')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'type')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'Question', 'column', 'type'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '0 单选 1 多选 2 填空 3 简答',
   'user', @CurrentUser, 'table', 'Question', 'column', 'type'
go

/*==============================================================*/
/* Table: QuestionOption                                        */
/*==============================================================*/
create table QuestionOption (
   oid                  int                  identity,
   pid                  int                  null,
   answer               nvarchar(255)        null,
   "right"              nvarchar(255)        null,
   score                numeric(4,1)         null,
   creator              int                  null,
   systime              datetime             null default getdate(),
   constraint PK_QUESTIONOPTION primary key (oid)
)
go

/*==============================================================*/
/* Table: Subject                                               */
/*==============================================================*/
create table Subject (
   sid                  int                  identity,
   name                 nvarchar(50)         not null,
   description          nvarchar(255)        null,
   ismenu               bit                  null,
   menuindex            int                  null,
   creator              int                  not null,
   systime              datetime             not null default getdate(),
   status               int                  not null default 1,
   constraint PK_SUBJECT primary key (sid)
)
go

/*==============================================================*/
/* Table: Video                                                 */
/*==============================================================*/
create table Video (
   vid                  int                  identity,
   title                nvarchar(100)        not null,
   cid                  int                  not null,
   cname                nvarchar(50)         not null,
   description          nvarchar(2000)       null,
   aid                  char(64)             not null,
   creator              int                  not null,
   systime              datetime             not null default getdate(),
   constraint PK_VIDEO primary key (vid)
)
go

